1. Field of the Invention
The present invention generally relates to error detection when heterogeneous software systems share common data, and more particularly, to a method and system for early detection of integrity constraint violations in application-database interactions.
2. Description of the Related Art
Many large-scale software applications, such as payroll systems, online stores and other web sites, travel applications, etc., involve interactions between applications and databases, where the application accesses a database (DB). Current application programming frameworks, such as the Java® 2 Enterprise Edition (J2EE) (See http://wwwjavasoft.com/j2ee), allow an application developer to specify a mapping between database tables and application object classes (as in object-oriented programming), from which objects are generated automatically at application runtime
The objects generated from a given mapping between application object classes and database table are simply proxies (e.g., windows or surrogates) into the actual data in the database. The idea is that the programmer gets an easy way to program against the DB, using notions and facilities more intuitive to a programmer (e.g., such as objects). Thus, the programmers do not have to worry about writing low-level database access code, for example, using JDBC. The underlying system uses the mapping provided by the developer between application object classes and database tables to manage interactions between the application and the database automatically.
A problem with current application programming frameworks is that databases in general have notions of integrity constraints on tables, whereas application object classes typically do not have a corresponding notion. When window/proxy objects are created from data in the database based on the mapping provided by a programmer, then such objects typically do not contain information about the integrity constraints on the data in the database. Once the application has finished working with the object view of the database, and attempts to update the database with the data in the object view, an error may arise if the data do not satisfy the database system's constraints. To summarize, when the underlying system created this object view, these DB constraints have been forgotten and only when the data is to be placed again in the DB does the error arise. This error may result in much loss of work and data by the application.
First, background on database integrity constraints is provided below. Database integrity constraints are application-independent assertions about the database content and its allowed transformations. Data types can be thought of as elementary constraints that limit the set of allowed content values. Similarly, a NOT NULL constraint states that NULL is not among the allowed values.
Primary key and unique constraints assert that the value combinations associated with the mentioned columns are unique within a relation. Check constraints are associated with a relation (e.g., the checked relation). Check constraints allow more elaborate verification at the tuple-level. Check constraints are usually intra-relational (e.g., refer to the value in a certain column or relates values in different columns of a tuple). In SQL 99 [Peter Gulutzan and Trudy Pelzer, “SQL 99 Complete—Really,” CMP Books 1999] relationships to arbitrary other tables are allowed. Assertions can be thought upon as stand-alone check constraints usually referring to more than one table. Assertions also apply at the table rather than the tuple level.
Foreign keys are columns in one relation that refer to columns in another parent relation such that the columns combination at the parent is declared as either unique or a primary key. In specifying a foreign key, the database designer has the option of specifying what happens if a parent relation tuple is deleted (or updated) while being pointed to by foreign key references from other relations. The basic options are to block the deletion, to cascade it (e.g., to delete or update the pointing tuples), to set the pointing columns to NULL, or to set them to a default value.
For example, as shown in FIG. 1, some constraints might include that each employee must have a manager, if the employee is in the dept “USSales”, her salary should be less than her manager's, a manager's salary must be within a certain range, etc. Typically, these types of constraints cannot be expressed easily in a programming language. Specifically, consider an application class, Employee, with fields {NAME, DEPT, MGRID, SALARY} that are mapped to the corresponding columns of the EMPLOYEE relation. In current application frameworks, a constraint such as the constraint C5 of FIG. 1 on the database is not generally manifest in the declaration of a class Employee in a programming language. The programmer must write explicit code to ensure that instances of the Employee object do not violate that constraint.
Since current application frameworks offer little support for handling database integrity constraints at the application-level, a programmer must explicitly ensure that an application will not cause integrity constraint violations. This is generally done by inserting explicit checks by hand into the application code to enforce these constraints. If the database integrity constraints were to change over time, then the application code would also have to be modified to reflect the new integrity constraints. Finding all places where these checks had been inserted by hand is an error-prone and time-consuming process.
Thus, it has been difficult to ensure the database's integrity constraints are manifest in the application classes to which the database is mapped. Following the example above, when the application creates an employee, it may do so without a manager or the salary field might not be in the appropriate range. Significant work may be lost when eventually the application interacts with the database to insert the data corresponding to the Employee object into the database and the database signals an integrity violation error.
Thus, prior to the invention, there has been no technique which would consider the database integrity constraints and the mapping between database tables and application classes to ensure that the application will not cause database integrity constraint violations. Hence, there have been drawbacks in application development and performance.